<?php

include_once '../commons/Conexion.php';
include_once '../model/Avance.php';
include_once '../model/Categoria.php';
include_once '../model/CategoriaAvance.php';
include_once '../model/Constructora.php';
include_once '../model/Foto.php';
include_once '../model/Item.php';
include_once '../model/ItemAvance.php';
include_once '../model/Usuario.php';
include_once 'ConstrucDao.php';
include_once 'ObraDao.php';
include_once 'EtapaDao.php';
include_once 'CategoriaDao.php';
include_once 'UsuarioDao.php';

/**
 * Description of AvanceDao
 *
 * @author User
 */
class AvanceDao {

    private $tAvance = 't004_avance';
    private $tFotos = 't005_fotos';
    private $tCatAvance = 't006_categoria_avance';
    private $tItemAvance = 't007_item_avance';

    function __construct() {
        
    }

    /* @var $oAvance Avance 
     * @result $oAvance Avance
     */

    public function saveAvance($oAvance = null) {

        $oAvance->setId(0);
        try {
            $query = 'insert into %s (tx_observacion,
                                    fe_registro,
                                    in_cerrado,
                                    fk_inspector,
                                    fk_etapa) 
                values ("%s","%s","%s",%d,%d);';

            $query = sprintf($query, $this->tAvance, $oAvance->getObservaciones(), $oAvance->getFechaAvance(), $oAvance->getCerrado(), $oAvance->getInspector()->getId(), $oAvance->getEtapa()->getId());

            $conn = new Conexion();
            $conn->open();
            $conn->query($query);

            $query = 'select l.co_id from %s l where l.tx_observacion = "%s" and fe_registro = "%s" and fk_inspector = %d  and fk_etapa = %d;';
            $query = sprintf($query, $this->tAvance, $oAvance->getObservaciones(), $oAvance->getFechaAvance(), $oAvance->getInspector()->getId(), $oAvance->getEtapa()->getId());

            $result = $conn->query($query);

            while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                $oAvance->setId($fila['co_id']);
            }

            $conn->close();
        } catch (Exception $e) {
            
        }

        return $oAvance;
    }

    /*
     * @var $lId long
     * @result $oAvance Avance
     */

    public function getAvanceById($lId = 0) {

        $oAvance = null;

        try {
            $query = 'select l.* from %s l where l.co_id = %d order by l.co_id;';
            $query = sprintf($query, $this->tAvance, $lId);

            $conn = new Conexion();
            $conn->open();

            $result = $conn->query($query);

            if (isset($result)) {
                $eDao = new EtapaDao();
                $uDao = new UsuarioDao();
                while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                    $oAvance = new Avance();
                    $oAvance->setId($fila['co_id']);
                    $oAvance->setObservaciones($fila['tx_observacion']);
                    $oAvance->setFechaAvance($fila['fe_registro']);
                    $oAvance->setCerrado($fila['in_cerrado']);
                    $oAvance->setInspector($uDao->getUsuarioById($fila['fk_inspector']));
                    $oAvance->setEtapa($eDao->getEtapaById($fila['fk_etapa']));
                    $oAvance->setFotos($this->getFotosByAvance($fila['co_id']));
                    $oAvance->setCategorias($this->getCategoriasByAvance($fila['co_id']));
                }
            }

            $conn->close();
        } catch (Exception $e) {
            
        }
        return $oAvance;
    }

    /*
     * @var $inicio date
     * @var $fin date
     * @return $listAvances List<Avance>
     */

    public function getAvanceByDate($inicio, $fin) {

        $listAvances = null;

        try {
            $query = 'select * from %s where fe_registro between "%s" and "%s" order by co_id;';
            $query = sprintf($query, $this->tAvance, $inicio, $fin);

            $conn = new Conexion();
            $conn->open();

            $result = $conn->query($query);

            $listAvances = array();
            $i = 0;
            if (isset($result)) {
                $eDao = new EtapaDao();
                $uDao = new UsuarioDao();
                while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                    $oAvance = new Avance();
                    $oAvance->setId($fila['co_id']);
                    $oAvance->setObservaciones($fila['tx_observacion']);
                    $oAvance->setFechaAvance($fila['fe_registro']);
                    $oAvance->setCerrado($fila['in_cerrado']);
                    $oAvance->setInspector($uDao->getUsuarioById($fila['fk_inspector']));
                    // $oAvance->setEtapa($eDao->getEtapaById($fila['fk_etapa']));
                    $listAvances[$i++] = $oAvance;
                }
            }
            $conn->close();
        } catch (Exception $e) {
            
        }

        return $listAvances;
    }

    /*
     * 
     * @return $listAvances List<Avance>
     */

    public function getAvance() {

        $listAvances = null;

        try {
            $query = 'select l.* from %s l order by l.co_id';
            $query = sprintf($query, $this->tAvance);

            $conn = new Conexion();
            $conn->open();

            $result = $conn->query($query);

            $listAvances = array();
            $i = 0;
            if (isset($result)) {
                $eDao = new EtapaDao();
                $uDao = new UsuarioDao();
                while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                    $oAvance = new Avance();
                    $oAvance->setId($fila['co_id']);
                    $oAvance->setObservaciones($fila['tx_observacion']);
                    $oAvance->setFechaAvance($fila['fe_registro']);
                    $oAvance->setCerrado($fila['in_cerrado']);
                    $oAvance->setInspector($uDao->getUsuarioById($fila['fk_inspector']));
                    $oAvance->setEtapa($eDao->getEtapaById($fila['fk_etapa']));
                    $listAvances[$i++] = $oAvance;
                }
            }
            $conn->close();
        } catch (Exception $e) {
            
        }

        return $listAvances;
    }

    /*
     * @var $lId long
     * @return $listAvances List<Avance>
     */

    public function getAvanceByEtapa($lId = 0) {

        $listAvances = null;

        try {
            $query = 'select l.* from %s l where l.fk_etapa = %d order by l.co_id';
            $query = sprintf($query, $this->tAvance, $lId);

            $conn = new Conexion();
            $conn->open();

            $result = $conn->query($query);

            $listAvances = array();
            $i = 0;
            if (isset($result)) {
                $eDao = new EtapaDao();
                $uDao = new UsuarioDao();
                while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                    $oAvance = new Avance();
                    $oAvance->setId($fila['co_id']);
                    $oAvance->setObservaciones($fila['tx_observacion']);
                    $oAvance->setFechaAvance($fila['fe_registro']);
                    $oAvance->setCerrado($fila['in_cerrado']);
                    $oAvance->setInspector($uDao->getUsuarioById($fila['fk_inspector']));
                    //  $oAvance->setEtapa($eDao->getEtapaById($fila['fk_etapa']));
                    $listAvances[$i++] = $oAvance;
                }
            }
            $conn->close();
        } catch (Exception $e) {
            
        }

        return $listAvances;
    }

    /*
     * @var $oAvance Avance
     * @result $oAvance Avance
     */

    public function updateAvance($oAvance) {

        try {
            $query = 'update %s l set 
                l.tx_observacion = "%s", 
                l.fe_registro = "%s", 
                l.in_cerrado = "%s", 
                l.fk_inspector = %d, 
                l.fk_etapa = %d 
                where  l.co_id = %d;';
            $query = sprintf($query, $this->tAvance, $oAvance->getObservaciones(), $oAvance->getFechaAvance(), $oAvance->getCerrado(), $oAvance->getInspector()->getId(), $oAvance->getEtapa()->getId(), $oAvance->getId());

            $conn = new Conexion();
            $conn->open();
            $conn->query($query);
            $conn->close();
        } catch (Exception $e) {
            $oAvance = null;
        }

        return $oAvance;
    }

    /*
     * @var $oAvance Avance
     * @return $result boolean
     */

    public function deleteAvance($oAvance) {

        $result = false;
        try {
            $query = 'delete from %s where co_id = %d';
            $query = sprintf($query, $this->tAvance, $oAvance->getId());

            $conn = new Conexion();
            $conn->open();
            $conn->query($query);
            $conn->close();

            $result = true;
        } catch (Exception $e) {
            
        }

        return $result;
    }

    /*
     * Data Access Object by FOTOS
     */

    /*
     * @var $oFoto Foto 
     * @result $oFoto Foto
     */

    public function saveFoto($oFoto = null) {

        $oFoto->setId(0);
        try {
            $query = 'insert into %s (nb_descripcion,
                                    bl_foto,
                                    fk_avance) 
                values ("%s","%s",%d);';

            $query = sprintf($query, $this->tFotos, $oFoto->getDescripcion(), $oFoto->getArchivo(), $oFoto->getAvance()->getId());

            $conn = new Conexion();
            $conn->open();
            $conn->query($query);

            $query = 'select l.co_id from %s l where l.nb_descripcion = "%s" and bl_foto = "%s" and fk_avance = %d;';

            $query = sprintf($query, $this->tFotos, $oFoto->getDescripcion(), $oFoto->getArchivo(), $oFoto->getAvance()->getId());

            $result = $conn->query($query);

            while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                $oFoto->setId($fila['co_id']);
            }

            $conn->close();
        } catch (Exception $e) {
            
        }

        return $oFoto;
    }

    /*
     * @var $lId long
     * @result $oFoto $oFoto
     */

    public function getFotoById($lId = 0) {

        $oFoto = null;

        try {
            $query = 'select l.* from %s l where l.co_id = %d order by l.co_id;';
            $query = sprintf($query, $this->tFoto, $lId);

            $conn = new Conexion();
            $conn->open();

            $result = $conn->query($query);

            if (isset($result)) {
                while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                    $oFoto = new Foto();
                    $oFoto->setId($fila['co_id']);
                    $oFoto->setDescripcion($fila['nb_descripcion']);
                    $oFoto->setArchivo($fila['bl_foto']);
                }
            }

            $conn->close();
        } catch (Exception $e) {
            
        }
        return $oFoto;
    }

    /*
     * 
     * @return $listFotos List<$oFoto>
     */

    public function getFotos() {

        $listFotos = null;

        try {
            $query = 'select l.* from %s l order by l.co_id';
            $query = sprintf($query, $this->tFotos);

            $conn = new Conexion();
            $conn->open();

            $result = $conn->query($query);

            $listFotos = array();
            $i = 0;
            if (isset($result)) {
                while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                    $oFoto = new Foto();
                    $oFoto->setId($fila['co_id']);
                    $oFoto->setDescripcion($fila['nb_descripcion']);
                    $oFoto->setArchivo($fila['bl_foto']);
                    $listFotos[$i++] = $oFoto;
                }
            }
            $conn->close();
        } catch (Exception $e) {
            
        }

        return $listFotos;
    }

    /*
     * @var $lId long
     * @return $listFotos List<$oFoto>
     */

    public function getFotosByAvance($lId = 0) {

        $listFotos = null;

        try {
            $query = 'select l.* from %s l where l.fk_avance = %d order by l.co_id';
            $query = sprintf($query, $this->tFotos, $lId);

            $conn = new Conexion();
            $conn->open();

            $result = $conn->query($query);

            $listFotos = array();
            $i = 0;
            if (isset($result)) {
                while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                    $oFoto = new Foto();
                    $oFoto->setId($fila['co_id']);
                    $oFoto->setDescripcion($fila['nb_descripcion']);
                    $oFoto->setArchivo($fila['bl_foto']);
                    $listFotos[$i++] = $oFoto;
                }
            }
            $conn->close();
        } catch (Exception $e) {
            
        }

        return $listFotos;
    }

    /*
     * @var $oFoto $oFoto
     * @result $oFoto $oFoto
     */

    public function updateFoto($oFoto) {

        try {
            $query = 'update %s l set 
                l.nb_descripcion = "%s", 
                l.bl_foto = "%s"
                where  l.co_id = %d;';
            $query = sprintf($query, $this->tFotos, $oFoto->getDescripcion(), $oFoto->getArchivo());

            $conn = new Conexion();
            $conn->open();
            $conn->query($query);
            $conn->close();
        } catch (Exception $e) {
            $oFoto = null;
        }

        return $oFoto;
    }

    /*
     * @var $oFoto $oFoto
     * @return $result boolean
     */

    public function deleteFoto($oFoto) {

        $result = false;
        try {
            $query = 'delete from %s where co_id = %d';
            $query = sprintf($query, $this->tFoto, $oFoto->getId());

            $conn = new Conexion();
            $conn->open();
            $conn->query($query);
            $conn->close();

            $result = true;
        } catch (Exception $e) {
            
        }

        return $result;
    }

    /*
     * Data Access Object by CATEGORIA_AVANCE
     */

    /*
     * @var $oCat CategoriaAvance 
     * @result $oCat CategoriaAvance
     */

    public function saveCategoria($oCat = null) {

        $oCat->setId(0);
        try {
            $query = 'insert into %s (tx_observaciones,
                                    pc_avance,
                                    fk_categoria,
                                    fk_avance) 
                values ("%s","%s",%d,%d);';

            $query = sprintf($query, $this->tCatAvance, $oCat->getObservaciones(), $oCat->getPorAvance(), $oCat->getCategoria()->getId(), $oCat->getAvance()->getId());

            $conn = new Conexion();
            $conn->open();
            $conn->query($query);

            $query = 'select l.co_id from %s l where 
                l.tx_observaciones = "%s" and 
                l.pc_avance = "%s" and 
                l.fk_categoria = %d and 
                l.fk_avance = %d;';

            $query = sprintf($query, $this->tCatAvance, $oCat->getObservaciones(), $oCat->getPorAvance(), $oCat->getCategoria()->getId(), $oCat->getAvance()->getId());

            $result = $conn->query($query);

            while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                $oCat->setId($fila['co_id']);
            }

            $conn->close();
        } catch (Exception $e) {
            
        }

        return $oCat;
    }

    /*
     * @var $lId long
     * @result $oCat CategoriaAvance
     */

    public function getCategoriaAvanceById($lId = 0) {

        $oCat = null;

        try {
            $query = 'select l.* from %s l where l.co_id = %d order by l.co_id;';
            $query = sprintf($query, $this->tCatAvance, $lId);

            $conn = new Conexion();
            $conn->open();

            $result = $conn->query($query);

            if (isset($result)) {
                $dao = new CategoriaDao();
                while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                    $oCat = new CategoriaAvance();
                    $oCat->setId($fila['co_id']);
                    $oCat->setObservaciones($fila['tx_observaciones']);
                    $oCat->setPorAvance($fila['pc_avance']);
                    $oCat->setCategoria($dao->getCatById($fila['fk_categoria']));
                    $oCat->setItems($this->getItemsByCategoriaAvance($fila['co_id']));
                }
            }

            $conn->close();
        } catch (Exception $e) {
            
        }
        return $oCat;
    }

    /*
     * 
     * @return $listCategorias List<CategoriaAvance>
     */

    public function getCatsAvance() {

        $listCategorias = null;

        try {
            $query = 'select l.* from %s l order by l.co_id';
            $query = sprintf($query, $this->tCatAvance);

            $conn = new Conexion();
            $conn->open();

            $result = $conn->query($query);

            $listCategorias = array();
            $i = 0;
            if (isset($result)) {
                $dao = new CategoriaDao();
                while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                    $oCat = new CategoriaAvance();
                    $oCat->setId($fila['co_id']);
                    $oCat->setObservaciones($fila['tx_observaciones']);
                    $oCat->setPorAvance($fila['pc_avance']);
                    $oCat->setCategoria($dao->getCatById($fila['fk_categoria']));
                    $oCat->setItems($this->getItemsByCategoriaAvance($fila['co_id']));
                    $listCategorias[$i++] = $oCat;
                }
            }
            $conn->close();
        } catch (Exception $e) {
            
        }

        return $listCategorias;
    }

    /*
     * @var $lId long
     * @return $listCategorias List<CategoriaAvance>
     */

    public function getCategoriasByAvance($lId = 0) {

        $listCategorias = null;

        try {
            $query = 'select l.* from %s l where l.fk_avance = %d order by l.co_id';
            $query = sprintf($query, $this->tCatAvance, $lId);

            $conn = new Conexion();
            $conn->open();

            $result = $conn->query($query);

            $listCategorias = array();
            $i = 0;
            if (isset($result)) {
                $dao = new CategoriaDao();
                while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                    $oCat = new CategoriaAvance();
                    $oCat->setId($fila['co_id']);
                    $oCat->setObservaciones($fila['tx_observaciones']);
                    $oCat->setPorAvance($fila['pc_avance']);
                    $oCat->setCategoria($dao->getCatById($fila['fk_categoria']));
                    $oCat->setItems($this->getItemsByCategoriaAvance($fila['co_id']));
                    $listCategorias[$i++] = $oCat;
                }
            }
            $conn->close();
        } catch (Exception $e) {
            
        }

        return $listCategorias;
    }

    /*
     * @var $oCat CategoriaAvance
     * @result $oCat CategoriaAvance
     */

    public function updateCatAvance($oCat) {

        try {
            $query = 'update %s l set 
                l.tx_observaciones = "%s", 
                l.pc_avance = "%s"
                where  l.co_id = %d;';
            $query = sprintf($query, $this->tCatAvance, $oCat->getObservaciones(), $oCat->getPorAvance(), $oCat->getId());

            $conn = new Conexion();
            $conn->open();
            $conn->query($query);
            $conn->close();
        } catch (Exception $e) {
            $oFoto = null;
        }

        return $oFoto;
    }

    /*
     * @var $oCat CategoriaAvance
     * @return $result boolean
     */

    public function deleteCatAvance($oCat) {

        $result = false;
        try {
            $query = 'delete from %s where co_id = %d';
            $query = sprintf($query, $this->tCatAvance, $oCat->getId());

            $conn = new Conexion();
            $conn->open();
            $conn->query($query);
            $conn->close();

            $result = true;
        } catch (Exception $e) {
            
        }

        return $result;
    }

    /*
     * Data Access Object by ITEM_AVANCE
     */

    /*
     * @var $oItem ItemAvance 
     * @result $oItem ItemAvance
     */

    public function saveItem($oItem = null) {

        $oItem->setId(0);
        try {
            $query = 'insert into %s (fe_inicio,
                                    fe_fin,
                                    tx_conforme,
                                    fk_item,
                                    fk_categoria_avance) 
                values ("%s","%s","%s",%d,%d);';

            $query = sprintf($query, $this->tItemAvance, $oItem->getFechaInicio(), $oItem->getFechaFin(), $oItem->getConforme(), $oItem->getItem()->getId(), $oItem->getCategoriaAvance()->getId());

            $conn = new Conexion();
            $conn->open();
            $conn->query($query);

            $query = 'select l.co_id from %s l where 
                l.fe_inicio = "%s" and 
                l.fe_fin = "%s" and 
                l.tx_conforme = "%s" and 
                l.fk_item = %d and
                l.fk_categoria_avance = %d;';

            $query = sprintf($query, $this->tItemAvance, $oItem->getFechaInicio(), $oItem->getFechaFin(), $oItem->getConforme(), $oItem->getItem()->getId(), $oItem->getCategoriaAvance()->getId());

            $result = $conn->query($query);

            while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                $oItem->setId($fila['co_id']);
            }

            $conn->close();
        } catch (Exception $e) {
            
        }

        return $oItem;
    }

    /*
     * @var $lId long
     * @result $oItem ItemAvance
     */

    public function getItemAvanceById($lId = 0) {

        $oItem = null;

        try {
            $query = 'select l.* from %s l where l.co_id = %d order by l.co_id;';
            $query = sprintf($query, $this->tItemAvance, $lId);

            $conn = new Conexion();
            $conn->open();

            $result = $conn->query($query);

            if (isset($result)) {
                $dao = new CategoriaDao();
                while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                    $oItem = new ItemAvance();
                    $oItem->setId($fila['co_id']);
                    $oItem->setFechaInicio($fila['fe_inicio']);
                    $oItem->setFechaFin($fila['fe_fin']);
                    $oItem->setConforme($fila['tx_conforme']);
                    $oItem->setItem($dao->getItemById($fila['fk_item']));
                }
            }

            $conn->close();
        } catch (Exception $e) {
            
        }
        return $oItem;
    }

    /*
     * 
     * @return $listItems List<ItemAvance>
     */

    public function getItemsAvance() {

        $listItems = null;

        try {
            $query = 'select l.* from %s l order by l.co_id';
            $query = sprintf($query, $this->tItemAvance);

            $conn = new Conexion();
            $conn->open();

            $result = $conn->query($query);

            $listItems = array();
            $i = 0;
            if (isset($result)) {
                $dao = new CategoriaDao();
                while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                    $oItem = new ItemAvance();
                    $oItem->setId($fila['co_id']);
                    $oItem->setFechaInicio($fila['fe_inicio']);
                    $oItem->setFechaFin($fila['fe_fin']);
                    $oItem->setConforme($fila['tx_conforme']);
                    $oItem->setItem($dao->getItemById($fila['fk_item']));
                    $listItems[$i++] = $oItem;
                }
            }
            $conn->close();
        } catch (Exception $e) {
            
        }

        return $listItems;
    }

    /*
     * @var $lId long
     * @return $listItems List<ItemAvance>
     */

    public function getItemsByCategoriaAvance($lId = 0) {

        $listItems = null;

        try {
            $query = 'select l.* from %s l where l.fk_categoria_avance = %d order by l.co_id';
            $query = sprintf($query, $this->tItemAvance, $lId);

            $conn = new Conexion();
            $conn->open();

            $result = $conn->query($query);

            $listItems = array();
            $i = 0;
            if (isset($result)) {
                $dao = new CategoriaDao();
                while ($fila = @mysql_fetch_array($result, MYSQL_ASSOC)) {
                    $oItem = new ItemAvance();
                    $oItem->setId($fila['co_id']);
                    $oItem->setFechaInicio($fila['fe_inicio']);
                    $oItem->setFechaFin($fila['fe_fin']);
                    $oItem->setConforme($fila['tx_conforme']);
                    $oItem->setItem($dao->getItemById($fila['fk_item']));
                    $listItems[$i++] = $oItem;
                }
            }
            $conn->close();
        } catch (Exception $e) {
            
        }

        return $listItems;
    }

    /*
     * @var $oItem ItemAvance
     * @result $oItem ItemAvance
     */

    public function updateItemAvance($oItem) {

        try {
            $query = 'update %s l set 
                l.fe_inicio = "%s", 
                l.fe_fin = "%s",
                l.tx_conforme = "%s"
                where  l.co_id = %d;';
            $query = sprintf($query, $this->tItemAvance, $oItem->getFechaInicio(), $oItem->getFechaFin(), $oItem->getConforme(), $oItem->getId());

            $conn = new Conexion();
            $conn->open();
            $conn->query($query);
            $conn->close();
        } catch (Exception $e) {
            $oItem = null;
        }

        return $oItem;
    }

    /*
     * @var $oItem ItemAvance
     * @return $result boolean
     */

    public function deleteItemAvance($oItem) {

        $result = false;
        try {
            $query = 'delete from %s where co_id = %d';
            $query = sprintf($query, $this->tItemAvance, $oItem->getId());

            $conn = new Conexion();
            $conn->open();
            $conn->query($query);
            $conn->close();

            $result = true;
        } catch (Exception $e) {
            
        }

        return $result;
    }

    /*
     * @var $lEtapa long
     * @return $result boolean
     */

    public function closeAvancesByEtapa($lEtapa) {

        $result = false;
        try {
            $query = "update %s l set l.in_cerrado = 'S' where l.fk_etapa = %d";
            $query = sprintf($query, $this->tAvance, $lEtapa);

            $conn = new Conexion();
            $conn->open();
            $conn->query($query);
            $conn->close();

            $result = true;
        } catch (Exception $e) {
            
        }

        return $result;
    }

}

?>
